package com.ssy.lingxi.report.dao.settleaccount;

import com.ssy.lingxi.report.model.bo.StatisticsSettlementBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 会员结算DAO
 * @author 伍将
 * @date 2020/11/26
 * @version 2.0.0
 */
@Service
public class MemberSettlementDAO {

    @Autowired
    @Qualifier("settleAccountJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询应付账款结算统计计数
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<StatisticsSettlementBO> getPayableStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" pay_member_id member_id,pay_role_id role_id,")
                .append(" SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS ToBeAccountsCount,")
                .append(" SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS ToBePayCount,")
                .append(" SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS ToBeReceiptCount,")
                .append(" SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS CompleteCount")
                .append(" from sa_member_settlement")
                .append(" group by pay_member_id,pay_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsSettlementBO.class));
    }

    /**
     * 查询应收账款结算统计计数
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<StatisticsSettlementBO> getReceivableStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" coalesce(a.member_id,b.member_id) member_id,")
                .append(" coalesce(a.role_id,b.role_id) role_id,")
                .append(" coalesce(a.ToBeAccountsCount,0) ToBeAccountsCount,")
                .append(" coalesce(a.ToBePayCount,0) ToBePayCount,")
                .append(" coalesce(a.ToBeReceiptCount,0) ToBeReceiptCount,")
                .append(" coalesce(a.CompleteCount,0) CompleteCount,")
                .append(" coalesce(b.ToBeInvoice,0) ToBeInvoice")
                .append(" from(")
                .append(" select member_id, role_id,")
                .append(" SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS ToBeAccountsCount,")
                .append(" SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS ToBePayCount,")
                .append(" SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS ToBeReceiptCount,")
                .append(" SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS CompleteCount")
                .append(" from sa_member_settlement")
                .append(" group by member_id,role_id")
                .append(" )a")
                .append(" full join(")
                .append(" select member_id,role_id,count(id) ToBeInvoice")
                .append(" from sa_member_receipt_invoice")
                .append(" where invoice_status=0")
                .append(" group by member_id,role_id")
                .append(" )b")
                .append(" on a.member_id=b.member_id")
                .append(" and a.role_id=b.role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsSettlementBO.class));

    }
}